package com.sfy.les.excel;

import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.List;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.sfy.les.db.QueryByJDBC;
import com.sfy.les.dictionary.DataBaseDictionary;
import com.sfy.les.dictionary.DictionaryInfo;
import com.sfy.les.table.TableFiledInfo;
import com.sfy.les.table.TableIndexInfo;
import com.sfy.les.table.TableInfo;
import com.sfy.les.utils.Common;

public class ExportExcel {
	
	private static final Logger logger = LoggerFactory.getLogger(ExportExcel.class);
	
	/**
	 * * 导出数据字典
	 * @param exportParam 导出的参数
	 * @param dictionaryInfo 数据库表信息
	 * @throws Exception 
	 * @return 返回文件路径
	 * @throws Exception
	 */
	public static String exportExcel(DataBaseDictionary exportParam, DictionaryInfo dictionaryInfo) throws Exception{
		logger.info("导出excel开始...");
		logger.info("开始检查传入参数...");
		checkParam(exportParam);
		logger.info("检查传入参数完成，开始获取数据库表信息...");
		List<TableInfo> tableInfoList = QueryByJDBC.queryTableInfo(exportParam, dictionaryInfo);
		//获取表名称与sheet页名称的对应关系
		HashMap<String, String> tableNameSheetNameMap = ExportUtils.getTableNameSheetNameMap(tableInfoList);
		logger.info("表信息获取完成，开始写入excel...");
		//获取导出文件名称
		String fileName = exportParam.getFileName();
		//建立新HSSFWorkbook对象  
		SXSSFWorkbook wb = new SXSSFWorkbook();
		/* 创建要用到的单元格样式 */
		ExportUtils.cresteCellStyle(wb);
		//新建首页
		createIndexSheet(tableInfoList, wb, fileName, tableNameSheetNameMap);
		//给每张表创建自己的sheet页
		for ( int i = 0; i < tableInfoList.size(); i++ ) {
			TableInfo tableInfo = tableInfoList.get(i);
			addTableSheet(wb, tableInfo, tableNameSheetNameMap);
			logger.info("表" + tableInfo.getTableName() + "信息写入excel完成...(" + (i+1) + "/" + tableInfoList.size() + ")");
		}
		logger.info("所有表信息已写入excel，正在导出...");
		//输出文件
		String filePath = exportParam.getFileUrl();
		if(filePath == null || "".equals(filePath)){
			filePath = "D:\\" + fileName + ".xlsx";
		}else{
			if(!filePath.endsWith("/") && !filePath.endsWith("\\")){
				filePath += "\\";
			}
			filePath = filePath + fileName + ".xlsx";
		}
		Common.ifExists(filePath);
        FileOutputStream fileOut = new FileOutputStream(filePath);  
        wb.write(fileOut);
        fileOut.flush();
        fileOut.close();
        logger.info("导出数据字典完成！导出目录：" + filePath);
        return filePath;
	}
	
	/**
	 * 检查传入的参数
	 * @return
	 */
	private static void checkParam(DataBaseDictionary exportParam) throws Exception {
		String dbUrl = exportParam.getDbUrl();
		if(dbUrl == null || "".equals(dbUrl)){
			logger.error("检查错误：请传入数据库地址dbUrl！");
			throw new Exception("检查错误：请传入数据库地址dbUrl！");
		}
		String dbName = exportParam.getDbName();
		if(dbName == null || "".equals(dbName)){
			logger.error("检查错误：请传入数据库名称dbName！");
			throw new Exception("检查错误：请传入数据库名称dbName！");
		}
		String dbUserName = exportParam.getDbUserName();
		if(dbUserName == null || "".equals(dbUserName)){
			logger.error("检查错误：请传入数据库登录用户名dbUserName！");
			throw new Exception("检查错误：请传入数据库登录用户名dbUserName！");
		}
		String dbPassword = exportParam.getDbPassword();
		if(dbPassword == null || "".equals(dbPassword)){
			logger.error("检查错误：请传入数据库登录用户密码dbPassword！");
			throw new Exception("检查错误：请传入数据库登录用户密码dbPassword！");
		}
		String fileName = exportParam.getFileName();
		if(fileName == null || "".equals(fileName)){
			logger.error("检查错误：请传入导出文件名称fileName！");
			throw new Exception("检查错误：请传入导出文件名称fileName！");
		}
	}
	
	/**
	 * 创建首页sheet
	 * @param dictionaryInfo 表信息
	 */
	private static void createIndexSheet(List<TableInfo> tableInfoList, SXSSFWorkbook wb, String fileName, HashMap<String,String> tableNameSheetNameMap){
		int tmpRow = -1;
		
		/* 建立新的sheet对象*/  
        SXSSFSheet sheet1 = wb.createSheet("数据结构清单");
        for (int i = 0; i < 6; i++) {
			sheet1.setColumnWidth(i, 6000);
		}
        
        //标题行
        SXSSFRow titelRow = sheet1.createRow((short)++tmpRow);
        CellRangeAddress dateRange = new CellRangeAddress(tmpRow, tmpRow, 0, 5);//起始行,结束行,起始列,结束列
        sheet1.addMergedRegion(dateRange);
        
        //添加单元格
        ExportUtils.addCell(wb, sheet1, titelRow, 0, fileName, ExportUtils.titleStyle, dateRange);
        
        //创建新的一行，存放数据库信息头
  		SXSSFRow dataBaseInfoRow = sheet1.createRow((short)++tmpRow);
  		String[] dataBaseInfoArr = ExportUtils.getDataBaseInfoArr();
  		for (int i = 0; i < dataBaseInfoArr.length; i++) {
  			ExportUtils.addCell(wb, sheet1, dataBaseInfoRow, i, dataBaseInfoArr[i], ExportUtils.tableTitleStyle, null);
  		}
  		
  		//创建新的一行，存放数据库信息头
		SXSSFRow dataBaseInfoRow2 = sheet1.createRow((short)++tmpRow);
		for (int i = 0; i < dataBaseInfoArr.length; i++) {
			SXSSFCell tempCell = dataBaseInfoRow2.createCell(i);
			tempCell.setCellValue("");
			tempCell.setCellStyle(ExportUtils.tableInfoTanCenterStyle);
		}
		
		//创建新一行，显示数据库表汇总
		SXSSFRow tableTotalRow = sheet1.createRow((short)++tmpRow);
		CellRangeAddress dateRange2 = new CellRangeAddress(tmpRow, tmpRow, 1, 4);//起始行,结束行,起始列,结束列
		sheet1.addMergedRegion(dateRange2);
		ExportUtils.addCell(wb, sheet1, tableTotalRow, 1, "系统数据库中共有表" + tableInfoList.size() + "张", ExportUtils.tableCountStyle, dateRange2);
	
		//创建新的一行，存放数据库的表名和注释信息
		SXSSFRow tableInfoRow = sheet1.createRow((short)++tmpRow);
		CellRangeAddress dateRange3 = new CellRangeAddress(tmpRow, tmpRow, 1, 2);//起始行,结束行,起始列,结束列
		CellRangeAddress dateRange4 = new CellRangeAddress(tmpRow, tmpRow, 3, 4);//起始行,结束行,起始列,结束列
		sheet1.addMergedRegion(dateRange3);
		sheet1.addMergedRegion(dateRange4);
		ExportUtils.addCell(wb, sheet1, tableInfoRow, 1, "表名", ExportUtils.tableTitleStyle, dateRange3);
		ExportUtils.addCell(wb, sheet1, tableInfoRow, 3, "注释", ExportUtils.tableTitleStyle, dateRange4);
		for (TableInfo tableInfo : tableInfoList) {
			//给每个表添加超连接
			int thisRow = ++tmpRow; 
			ExportUtils.addHyperLink(tableInfo, thisRow, sheet1, wb, tableNameSheetNameMap.get(tableInfo.getTableName()));
		}
	}
	
	/**
	 * 给每个表创建自己的sheet页
	 * @param wb
	 * @param tableInfo
	 */
	private static void addTableSheet(SXSSFWorkbook wb, TableInfo tableInfo, HashMap<String,String> tableNameSheetNameMap){
		//建立新sheet对象
		String sheetName = tableNameSheetNameMap.get(tableInfo.getTableName());
        SXSSFSheet tableSheet = wb.createSheet(sheetName);
        // 设置列的宽度 单位像素
        tableSheet.setColumnWidth(0, 8000);
        for (int i = 1; i < 8; i++) {
        	tableSheet.setColumnWidth(i, 5000);
		}
        tableSheet.setColumnWidth( 8, 12000);
        tableSheet.setColumnWidth(9, 4000);
        int tmpRow = -1;
        //创建新的一行，存放数据库的表名和注释信息
  		SXSSFRow tableInfoRow = tableSheet.createRow((short)++tmpRow);
		CellRangeAddress dateRange2 = new CellRangeAddress(tmpRow, tmpRow, 1, 4);//起始行,结束行,起始列,结束列
		tableSheet.addMergedRegion(dateRange2);
		
		CellRangeAddress dateRange3 = new CellRangeAddress(tmpRow, tmpRow, 5, 8);//起始行,结束行,起始列,结束列
		tableSheet.addMergedRegion(dateRange3);
		
		ExportUtils.addCell(wb, tableSheet, tableInfoRow, 0, "表名/描述", ExportUtils.tableTitleStyle, null);
  		ExportUtils.addCell(wb, tableSheet, tableInfoRow, 1, tableInfo.getTableName(), ExportUtils.tableInfoRoseStyle, dateRange2);
  		ExportUtils.addCell(wb, tableSheet, tableInfoRow, 5, tableInfo.getTableDesc(), ExportUtils.tableInfoRoseStyle, dateRange3);
  		//返回首页
  		TableInfo backIndex = new TableInfo();
  		backIndex.setTableDesc("返回首页");
  		backIndex.setTableName("返回首页");
  		ExportUtils.addHyperCell(wb, tableSheet, tableInfoRow, 9, backIndex, ExportUtils.backHomeStyle, null, "返回首页");
  		
  		//创建新的一行，存放数据库表表信息头
		SXSSFRow tableTitleRow = tableSheet.createRow((short)++tmpRow);
		String[] dataBaseInfoArr = ExportUtils.getTableInfoArr();
		for (int i = 0; i < dataBaseInfoArr.length; i++) {
			if(i == dataBaseInfoArr.length - 1){
				CellRangeAddress dateRange5 = new CellRangeAddress(tmpRow, tmpRow, 8, 9);//起始行,结束行,起始列,结束列
				tableSheet.addMergedRegion(dateRange5);
				ExportUtils.addCell(wb, tableSheet, tableTitleRow, i, dataBaseInfoArr[i], ExportUtils.tableTitleStyle, dateRange5);
			}else{
				ExportUtils.addCell(wb, tableSheet, tableTitleRow, i, dataBaseInfoArr[i], ExportUtils.tableTitleStyle, null);
			}
		}
		
		//表的字段信息
		List<TableFiledInfo> tableFiledInfoList = tableInfo.getTableFiledInfoList();
		for (TableFiledInfo tableFiledInfo : tableFiledInfoList) {
			SXSSFRow newRow = tableSheet.createRow((short)++tmpRow);
			//字段描述合并单元格
			CellRangeAddress dateRange = new CellRangeAddress(tmpRow, tmpRow, 8, 9);//起始行,结束行,起始列,结束列
			tableSheet.addMergedRegion(dateRange);
			ExportUtils.addCell(wb, tableSheet, newRow, 0, tableFiledInfo.getFieldName(), ExportUtils.tableInfoTanLeftStyle, null);
			ExportUtils.addCell(wb, tableSheet, newRow, 1, tableFiledInfo.getFieldType(), ExportUtils.tableInfoTanLeftStyle, null);
			ExportUtils.addCell(wb, tableSheet, newRow, 2, tableFiledInfo.getFieldLength(), ExportUtils.tableInfoTanCenterStyle, null);
			ExportUtils.addCell(wb, tableSheet, newRow, 3, tableFiledInfo.getFieldDecimalLength(), ExportUtils.tableInfoTanCenterStyle, null);
			ExportUtils.addCell(wb, tableSheet, newRow, 4, tableFiledInfo.getFieldIsEmpty(), ExportUtils.tableInfoTanCenterStyle, null);
			ExportUtils.addCell(wb, tableSheet, newRow, 5, tableFiledInfo.getFieldIsPrimaryKey(), ExportUtils.tableInfoTanCenterStyle, null);
			ExportUtils.addCell(wb, tableSheet, newRow, 6, tableFiledInfo.getFieldIsAutoIncere(), ExportUtils.tableInfoTanCenterStyle, null);
			ExportUtils.addCell(wb, tableSheet, newRow, 7, tableFiledInfo.getFieldDefaultValue(), ExportUtils.tableInfoTanCenterStyle, null);
			ExportUtils.addCell(wb, tableSheet, newRow, 8, tableFiledInfo.getFieldDesc(), ExportUtils.tableInfoTanLeftStyle, dateRange);
		}
		
		//创建新的一行，存放表的索引头信息
		SXSSFRow tableIndexTitleRow = tableSheet.createRow((short)++tmpRow);
		CellRangeAddress dateRange5 = new CellRangeAddress(tmpRow, tmpRow, 2, 4);//起始行,结束行,起始列,结束列
		tableSheet.addMergedRegion(dateRange5);
		CellRangeAddress dateRange6 = new CellRangeAddress(tmpRow, tmpRow, 5, 9);//起始行,结束行,起始列,结束列
		tableSheet.addMergedRegion(dateRange6);
		ExportUtils.addCell(wb, tableSheet, tableIndexTitleRow, 0, "索引类型", ExportUtils.tableTitleStyle, null);
		ExportUtils.addCell(wb, tableSheet, tableIndexTitleRow, 1, "索引方式", ExportUtils.tableTitleStyle, null);
		ExportUtils.addCell(wb, tableSheet, tableIndexTitleRow, 2, "索引名", ExportUtils.tableTitleStyle, dateRange5);
		ExportUtils.addCell(wb, tableSheet, tableIndexTitleRow, 5, "索引字段", ExportUtils.tableTitleStyle, dateRange6);
		//获取表的所有索引信息
		List<TableIndexInfo> tableIndexInfoList = tableInfo.getTableIndexInfoList();
		for (TableIndexInfo tableIndexInfo : tableIndexInfoList) {
			SXSSFRow newRow = tableSheet.createRow((short)++tmpRow);
			CellRangeAddress dateRange = new CellRangeAddress(tmpRow, tmpRow, 2, 4);//起始行,结束行,起始列,结束列
			tableSheet.addMergedRegion(dateRange);
			CellRangeAddress dateRangeF = new CellRangeAddress(tmpRow, tmpRow, 5, 9);//起始行,结束行,起始列,结束列
			tableSheet.addMergedRegion(dateRangeF);
			ExportUtils.addCell(wb, tableSheet, newRow, 0, tableIndexInfo.getIndexType(), ExportUtils.tableInfoGreenLeftStyle, null);
			ExportUtils.addCell(wb, tableSheet, newRow, 1, tableIndexInfo.getIndexStyle(), ExportUtils.tableInfoGreenLeftStyle, null);
			ExportUtils.addCell(wb, tableSheet, newRow, 2, tableIndexInfo.getIndexName(), ExportUtils.tableInfoGreenLeftStyle, dateRange);
			ExportUtils.addCell(wb, tableSheet, newRow, 5, tableIndexInfo.getIndexFields(), ExportUtils.tableInfoGreenLeftStyle, dateRangeF);
		}
		
		//创建新的一行，存放初始化脚本头信息
		SXSSFRow tableDdlTitleRow = tableSheet.createRow((short)++tmpRow);
		CellRangeAddress dateRange7 = new CellRangeAddress(tmpRow, tmpRow, 0, 9);//起始行,结束行,起始列,结束列
		tableSheet.addMergedRegion(dateRange7);
		ExportUtils.addCell(wb, tableSheet, tableDdlTitleRow, 0, "初始化脚本", ExportUtils.tableTitleStyle, dateRange7);
		//创建新的一行，存放初始化脚本信息
		SXSSFRow tableDdlRow = tableSheet.createRow((short)++tmpRow);
		tableDdlRow.setHeight((short) ((tableDdlRow.getHeight() + 100) * (tableFiledInfoList.size() + tableIndexInfoList.size() + 2)));
		CellRangeAddress dateRange8 = new CellRangeAddress(tmpRow, tmpRow, 0, 9);//起始行,结束行,起始列,结束列
		tableSheet.addMergedRegion(dateRange8);
		ExportUtils.addCell(wb, tableSheet, tableDdlRow, 0, tableInfo.getTableInitSql(), ExportUtils.tableInfoAquaCenterStyle, dateRange8);
	}

}
